using System;
using System.Collections.Generic;
using System.Text;

namespace DatabaseUnitTesting.Utilities.StoredProcedure
{
    internal static class SqlGenerator
    {
        internal static List<string> GenerateScript(Database data, string schemaName, string tableNameStub)
        {
            List<string> commands = new List<string>();
            string fullStub = schemaName + "." + tableNameStub;
            int num = 0;
            foreach(KeyValuePair<Table, int> table in data.Tables)
            {
                for (int i = 0; i < table.Value; i++)
                {
                    string tableName = fullStub + num;
                    GenerateDropTableScript(tableName, commands);
                    GenerateCreateTableScript(table.Key, tableName, commands);
                    GenerateInsertScript(table.Key, tableName, commands);
                    num++;
                }
            }
            return commands;
        }

        internal static void GenerateDropTableScript(string tableName, List<string> commands)
        {
            StringBuilder dropCommandText = new StringBuilder("--Dropping Existing Table ");
            dropCommandText.Append(tableName);
            dropCommandText.Append("\n IF OBJECT_ID('");
            dropCommandText.Append(tableName);
            dropCommandText.Append("') IS NOT NULL DROP TABLE ");
            dropCommandText.Append(tableName);
            commands.Add(dropCommandText.ToString());
        }

        internal static void GenerateCreateTableScript(Table table, string tableName, List<string> commands)
        {
            StringBuilder createCommand = new StringBuilder("--Creating Table ");
            createCommand.Append(tableName);
            createCommand.Append("\n CREATE TABLE ");
            createCommand.Append(tableName);
            createCommand.Append(" (");

            string schema = table.Schema.KeyString;
            string[] fields = schema.Split(new string[] {Row.DELIMITER}, StringSplitOptions.None);

            for(int i = 1; i < fields.Length; i++)
            {
                string[] parts = fields[i].Split(new string[] {Column.DELIMITER}, StringSplitOptions.None);
                createCommand.Append(parts[0]);
                createCommand.Append(" ");
                createCommand.Append(parts[1]);
                createCommand.Append(',');
            }
            createCommand.Remove(createCommand.Length - 1, 1);
            createCommand.Append(" )");
            commands.Add(createCommand.ToString());
        }

        internal static void GenerateInsertScript(Table table, string tableName, List<string> commands)
        {
            StringBuilder insertText = new StringBuilder("--Inserting Data Into ");
            insertText.Append(tableName);

            string[] fields = table.Schema.KeyString.Split(new string[] {Row.DELIMITER}, StringSplitOptions.None);
            List<string> columnList = new List<string>();
            List<string> typeList = new List<string>();
            for(int i = 1; i < fields.Length; i++)
            {
                string[] parts = fields[i].Split(new string[] {Column.DELIMITER}, StringSplitOptions.None);
                columnList.Add(parts[0]);
                typeList.Add(parts[1]);
            }

            foreach (KeyValuePair<string, int> row in table.Rows)
            {
                string[] values = new string[table.FieldCount];
                string[] columns =
                    row.Key.Split(new string[] {Row.DELIMITER}, StringSplitOptions.None);

                int b = 0;
                for(int j = 1; j < columns.Length; j++)
                {
                    string[] parts =
                        columns[j].Split(new string[] {Column.DELIMITER}, StringSplitOptions.None);
                    while(!columnList[b].Equals(parts[0]))
                    {
                        values[b] = "NULL";
                        b++;
                    }
                    if(typeList[b].Contains("binary"))
                        values[b] = parts[1];
                    else
                        values[b] = "'" + parts[1].Replace("'", "''") + "'";

                    b++;
                }
                for(; b < columnList.Count; b++)
                    values[b] = "NULL";

                insertText.Append("\n INSERT INTO ");
                insertText.Append(tableName);
                insertText.Append(" (");
                insertText.Append(String.Join(",", columnList.ToArray()));
                insertText.Append(") VALUES (");
                insertText.Append(String.Join(",", values));
                insertText.Append(") ");
            }
            commands.Add(insertText.ToString());
        }
    }
}